insert overwrite table jms_dm.dm_terminal_sign_summary_plan_sign_network_dt partition (dt)
-- 末端时效规划签收网点维度汇总
select end_manage_region_code--末端网点所属管理大区编码
     , max(end_manage_region_name)                 as end_manage_region_name--末端网点所属管理大区名称
     , end_agent_code--末端网点所属代理区编码
     , max(end_agent_name)                         as end_agent_name--末端网点所属代理区名称
     , max(end_provider_id)                        as end_provider_id--末端网点所属省份id
     , max(end_provider_name)                      as end_provider_name--末端网点所属省份名称
     , max(end_city_id)                            as end_city_id--末端网点所属城市id
     , max(end_city_name)                          as end_city_name--末端网点所属城市名称
     , max(end_area_id)                            as end_area_id--末端网点所属区县id
     , max(end_area_name)                          as end_area_name--末端网点所属区县名称
     , end_center_code--末端发件中心/集散编码
     , max(end_center_name)                        as end_center_name--末端发件中心/集散名称
     , end_franchisee_code--末端网点所属加盟商编码
     , max(end_franchisee_name)                    as end_franchisee_name--末端网点所属加盟商名称
     , final_sign_network_code--最终签收派件网点编码
     , max(final_sign_network_name)                as final_sign_network_name--最终签收派件网点名称
     , max(final_sign_user_name)                   as final_sign_user_name--最后签收派件员名称
     , sum(need_sign_count)                        as need_sign_count--应签收汇总
     , sum(aging_sign_in_time_network)             as aging_sign_in_time_network--时效签收准点-网点签收
     , sum(aging_sign_in_time_terminal_pdd)        as aging_sign_in_time_terminal_pdd--时效签收准点-终端入库(桃花岛认证)
     , sum(aging_sign_in_time_terminal_other)      as aging_sign_in_time_terminal_other--时效签收准点-终端入库(其他)
     , sum(aging_sign_in_time_proxy)               as aging_sign_in_time_proxy--时效签收准点-代理点收入
     , sum(aging_sign_in_time_transfer)            as aging_sign_in_time_transfer--时效签收准点-转邮
     , sum(aging_sign_in_time_count)               as aging_sign_in_time_count--时效签收准点-汇总
     --原始
     , sum(22_aging_sign_over_time_network)        as 22_aging_sign_over_time_network--时效签收延误-网点签收
     , sum(22_aging_sign_over_time_terminal_pdd)   as 22_aging_sign_over_time_terminal_pdd--时效签收延误-终端入库(桃花岛认证)
     , sum(22_aging_sign_over_time_terminal_other) as 22_aging_sign_over_time_terminal_other--时效签收延误-终端入库(其他)
     , sum(22_aging_sign_over_time_proxy)          as 22_aging_sign_over_time_proxy--时效签收延误-代理点收入
     , sum(22_aging_sign_over_time_difficult)      as 22_aging_sign_over_time_difficult--时效签收延误-问题件
     , sum(22_aging_sign_over_time_other)          as 22_aging_sign_over_time_other--时效签收延误-其他
     , sum(22_aging_sign_over_time_transfer)       as 22_aging_sign_over_time_transfer--时效签收延误-转邮
     , sum(22_aging_sign_over_time_sign_count)     as 22_aging_sign_over_time_sign_count--时效签收延误-已签收汇总
     , sum(22_aging_sign_over_time_nosign_count)   as 22_aging_sign_over_time_nosign_count--时效签收延误-未签收汇总
     --新增1  
     , sum(24_aging_sign_over_time_network)        as 24_aging_sign_over_time_network--时效签收延误-网点签收
     , sum(24_aging_sign_over_time_terminal_pdd)   as 24_aging_sign_over_time_terminal_pdd--时效签收延误-终端入库(桃花岛认证)
     , sum(24_aging_sign_over_time_terminal_other) as 24_aging_sign_over_time_terminal_other--时效签收延误-终端入库(其他)
     , sum(24_aging_sign_over_time_proxy)          as 24_aging_sign_over_time_proxy--时效签收延误-代理点收入
     , sum(24_aging_sign_over_time_difficult)      as 24_aging_sign_over_time_difficult--时效签收延误-问题件
     , sum(24_aging_sign_over_time_other)          as 24_aging_sign_over_time_other--时效签收延误-其他
     , sum(24_aging_sign_over_time_transfer)       as 24_aging_sign_over_time_transfer--时效签收延误-转邮
     , sum(24_aging_sign_over_time_sign_count)     as 24_aging_sign_over_time_sign_count--时效签收延误-已签收汇总
     , sum(24_aging_sign_over_time_nosign_count)   as 24_aging_sign_over_time_nosign_count--时效签收延误-未签收汇总
     --新增2
     , sum(00_aging_sign_over_time_network)        as 00_aging_sign_over_time_network--时效签收延误-网点签收
     , sum(00_aging_sign_over_time_terminal_pdd)   as 00_aging_sign_over_time_terminal_pdd--时效签收延误-终端入库(桃花岛认证)
     , sum(00_aging_sign_over_time_terminal_other) as 00_aging_sign_over_time_terminal_other--时效签收延误-终端入库(其他)
     , sum(00_aging_sign_over_time_proxy)          as 00_aging_sign_over_time_proxy--时效签收延误-代理点收入
     , sum(00_aging_sign_over_time_difficult)      as 00_aging_sign_over_time_difficult--时效签收延误-问题件
     , sum(00_aging_sign_over_time_other)          as 00_aging_sign_over_time_other--时效签收延误-其他
     , sum(00_aging_sign_over_time_transfer)       as 00_aging_sign_over_time_transfer--时效签收延误-转邮
     , sum(00_aging_sign_over_time_sign_count)     as 00_aging_sign_over_time_sign_count--时效签收延误-已签收汇总
     , sum(00_aging_sign_over_time_nosign_count)   as 00_aging_sign_over_time_nosign_count--时效签收延误-未签收汇总
     --结束
     , sum(aging_sign_24_count)                    as aging_sign_24_count--时效签收-24点前签收汇总
     , sum(actual_sign_in_time_network)            as actual_sign_in_time_network--实际签收准点-网点签收
     , sum(actual_sign_in_time_terminal)           as actual_sign_in_time_terminal--实际签收准点-驿站/快递柜
     , sum(actual_sign_in_time_proxy)              as actual_sign_in_time_proxy--实际签收准点-代理点收入
     , sum(actual_sign_in_time_count)              as actual_sign_in_time_count--实际签收准点-汇总
     , sum(actual_sign_over_time_network)          as actual_sign_over_time_network--实际签收延误-网点签收
     , sum(actual_sign_over_time_terminal)         as actual_sign_over_time_terminal--实际签收延误-驿站/快递柜
     , sum(actual_sign_over_time_proxy)            as actual_sign_over_time_proxy--实际签收延误-代理点收入
     , sum(actual_sign_over_time_difficult)        as actual_sign_over_time_difficult--实际签收延误-问题件
     , sum(actual_sign_over_time_other)            as actual_sign_over_time_other--实际签收延误-其他
     , sum(actual_sign_over_time_sign_count)       as actual_sign_over_time_sign_count--实际签收延误-已签收汇总
     , sum(actual_sign_over_time_nosign_count)     as actual_sign_over_time_nosign_count--实际签收延误-未签收汇总
     , sum(actual_sign_24_count)                   as actual_sign_24_count--实际签收-24点前签收汇总
     , dt                                          as final_plan_sign_date--规划签收日期
     , sum(aging_sign_00_count)                    as aging_sign_00_count --时效签收-24点前签收汇总
     , sum(aging_sign_22_count)                    as aging_sign_22_count --时效签收-24点前签收汇总
     , min(is_common_network)                      as is_common_network--是否共配网点
     --新增实际签收未维度  22前点汇总
     , sum(22_actual_sign_in_time_network)         as 22_actual_sign_in_time_network --22点前实际签收准点-网点签收
     , sum(22_actual_sign_in_time_terminal)        as 22_actual_sign_in_time_terminal --22点前实际签收准点-驿站/快递柜
     , sum(22_actual_sign_in_time_proxy)           as 22_actual_sign_in_time_proxy --22点前实际签收准点-代理点收入
     , sum(22_actual_sign_in_time_count)           as 22_actual_sign_in_time_count --22点前实际签收准点-汇总
     , sum(22_actual_sign_over_time_network)       as 22_actual_sign_over_time_network --22点前实际签收延误-网点签收
     , sum(22_actual_sign_over_time_terminal)      as 22_actual_sign_over_time_terminal --22点前实际签收延误-驿站/快递柜
     , sum(22_actual_sign_over_time_proxy)         as 22_actual_sign_over_time_proxy --22点前实际签收延误-代理点收入
     , sum(22_actual_sign_over_time_difficult)     as 22_actual_sign_over_time_difficult --22点前实际签收延误-问题件
     , sum(22_actual_sign_over_time_other)         as 22_actual_sign_over_time_other --22点前实际签收延误-其他
     , sum(22_actual_sign_over_time_sign_count)    as 22_actual_sign_over_time_sign_count --22点前实际签收延误-已签收汇总
     , sum(22_actual_sign_over_time_nosign_count)  as 22_actual_sign_over_time_nosign_count --22点前实际签收延误-未签收汇总
     --新增实际签收未维度  22-24点汇总
     , sum(24_actual_sign_in_time_network)         as 24_actual_sign_in_time_network --22-24点实际签收准点-网点签收
     , sum(24_actual_sign_in_time_terminal)        as 24_actual_sign_in_time_terminal --22-24点实际签收准点-驿站/快递柜
     , sum(24_actual_sign_in_time_proxy)           as 24_actual_sign_in_time_proxy --22-24点实际签收准点-代理点收入
     , sum(24_actual_sign_in_time_count)           as 24_actual_sign_in_time_count --22-24点实际签收准点-汇总
     , sum(24_actual_sign_over_time_network)       as 24_actual_sign_over_time_network --22-24点实际签收延误-网点签收
     , sum(24_actual_sign_over_time_terminal)      as 24_actual_sign_over_time_terminal --22-24点实际签收延误-驿站/快递柜
     , sum(24_actual_sign_over_time_proxy)         as 24_actual_sign_over_time_proxy --22-24点实际签收延误-代理点收入
     , sum(24_actual_sign_over_time_difficult)     as 24_actual_sign_over_time_difficult --22-24点实际签收延误-问题件
     , sum(24_actual_sign_over_time_other)         as 24_actual_sign_over_time_other --22-24点实际签收延误-其他
     , sum(24_actual_sign_over_time_sign_count)    as 24_actual_sign_over_time_sign_count--22-24点实际签收延误-已签收汇总
     , sum(24_actual_sign_over_time_nosign_count)  as 24_actual_sign_over_time_nosign_count--22-24实际签收延误-未签收汇总
     --新增实际签收未维度  超24点汇总
     , sum(00_actual_sign_in_time_network)         as 00_actual_sign_in_time_network --超24点实际签收准点-网点签收
     , sum(00_actual_sign_in_time_terminal)        as 00_actual_sign_in_time_terminal --超24点实际签收准点-驿站/快递柜
     , sum(00_actual_sign_in_time_proxy)           as 00_actual_sign_in_time_proxy --超24点实际签收准点-代理点收入
     , sum(00_actual_sign_in_time_count)           as 00_actual_sign_in_time_count --超24点实际签收准点-汇总
     , sum(00_actual_sign_over_time_network)       as 00_actual_sign_over_time_network --超24点实际签收延误-网点签收
     , sum(00_actual_sign_over_time_terminal)      as 00_actual_sign_over_time_terminal --超24点实际签收延误-驿站/快递柜
     , sum(00_actual_sign_over_time_proxy)         as 00_actual_sign_over_time_proxy --超24点实际签收延误-代理点收入
     , sum(00_actual_sign_over_time_difficult)     as 00_actual_sign_over_time_difficult --超24点实际签收延误-问题件
     , sum(00_actual_sign_over_time_other)         as 00_actual_sign_over_time_other --超24点实际签收延误-其他
     , sum(00_actual_sign_over_time_sign_count)    as 00_actual_sign_over_time_sign_count--超24点实际签收延误-已签收汇总
     , sum(00_actual_sign_over_time_nosign_count)  as 00_actual_sign_over_time_nosign_count--超24点实际签收延误-未签收汇总
     , sum(actual_sign_22_count)                   as actual_sign_22_count -- 22点签收量
     , sum(actual_sign_00_count)                   as actual_sign_00_count --24点前前签收量
     ,order_source_name
     , dt --分区日期
from jms_dm.dm_terminal_sign_summary_plan_sign_new_dt t
where dt ='{{ execution_date | cst_ds }}'
group by dt,
         end_manage_region_code,
         end_agent_code,
         end_center_code,
         end_franchisee_code,
         final_sign_network_code,
         order_source_name
 distribute by  10
;
